USE MDS_Platon
GO
SET NOCOUNT ON
PRINT 'SCHEMA GbrCommon, PROC Ex, Ex_I'
BEGIN TRY
	EXEC sp_executesql N'CREATE SCHEMA GbrCommon'
	EXEC sp_executesql N'CREATE PROC GbrCommon.Ex @comment NVARCHAR(4000), @cmd NVARCHAR(MAX) AS IF @comment IS NOT NULL PRINT @comment; EXEC sp_executesql @cmd'
	EXEC GbrCommon.Ex 'PROC GbrCommon.Ex_I', 'CREATE PROC GbrCommon.Ex_I @comment NVARCHAR(4000), @cmd NVARCHAR(MAX) AS BEGIN TRY EXEC GbrCommon.Ex @comment,@cmd; END TRY BEGIN CATCH END CATCH'
END TRY BEGIN CATCH END CATCH
PRINT 'Eo SCHEMA GbrCommon, PROC Ex, Ex_I'
GO
PRINT 'ID functions'
EXEC GbrCommon.Ex_I 'FUNCTION GbrCommon.ModelID', 'CREATE FUNCTION GbrCommon.ModelID(@m NVARCHAR(4000)) RETURNS INT AS BEGIN RETURN (SELECT ID FROM [mdm].[tblModel] WHERE [Name]=@m) END' -- SELECT GbrCommon.ModelID('Customer')
EXEC GbrCommon.Ex_I 'FUNCTION GbrCommon.EntityID', 'CREATE FUNCTION GbrCommon.EntityID(@m NVARCHAR(4000),@e NVARCHAR(4000)) RETURNS INT AS BEGIN RETURN (SELECT ID FROM [mdm].[tblEntity] WHERE Model_ID=GbrCommon.ModelID(@m) AND [Name]=@e) END' -- SELECT GbrCommon.EntityID('Customer','Currency')
EXEC GbrCommon.Ex_I 'FUNCTION GbrCommon.ModelID_EntityID', 'CREATE FUNCTION GbrCommon.ModelID_EntityID(@m NVARCHAR(4000),@e NVARCHAR(4000)) RETURNS NVARCHAR(4000) AS BEGIN RETURN (SELECT CAST(GbrCommon.ModelID(@m) AS NVARCHAR(4000))) + ''_'' + (SELECT CAST(GbrCommon.EntityID(@m, @e) AS NVARCHAR(4000))) END'-- SELECT GbrCommon.ModelID_EntityID('Customer','Currency')
EXEC GbrCommon.Ex_I 'PROC GbrCommon.ModelID_EntityID', 'CREATE PROC GbrCommon.Entity @m NVARCHAR(4000),@e NVARCHAR(4000) AS
DECLARE @c NVARCHAR(4000)=''SELECT * FROM mdm.tbl_'' + (SELECT GbrCommon.ModelID_EntityID(@m,@e)) + ''_EN'';EXEC sp_executesql @c'
GO
EXEC GbrCommon.Entity 'Customer','Currency'
GO
PRINT 'Eo ID functions'
GO
